package com.team9.fitness.mapper;

import com.team9.fitness.entity.CommunityPost;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Optional;

/**
 * 社区帖子数据访问层接口
 * 
 * 提供社区帖子数据的CRUD操作：
 * - 帖子管理：插入、更新、删除帖子
 * - 查询功能：支持按用户、ID查询，支持排序
 * - 统计功能：统计帖子的点赞、喜欢、评论数量
 * - 状态管理：支持用户点赞、喜欢状态查询
 */
@Mapper
public interface CommunityPostMapper {

    @Insert("INSERT INTO community_post (user_id, title, content, like_count, favorite_count, comment_count, created_at, updated_at) " +
            "VALUES (#{userId}, #{title}, #{content}, #{likeCount}, #{favoriteCount}, #{commentCount}, #{createdAt}, #{updatedAt})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(CommunityPost post);

    @Select("SELECT cp.*, " +
            "(SELECT COUNT(*) FROM post_like pl WHERE pl.post_id = cp.id AND pl.is_liked = TRUE) as like_count, " +
            "(SELECT COUNT(*) FROM post_favorite pf WHERE pf.post_id = cp.id AND pf.is_favorited = TRUE) as favorite_count, " +
            "(SELECT COUNT(*) FROM post_comment pc WHERE pc.post_id = cp.id) as comment_count, " +
            "(SELECT pl.is_liked FROM post_like pl WHERE pl.user_id = #{currentUserId} AND pl.post_id = cp.id) as is_liked, " +
            "(SELECT pf.is_favorited FROM post_favorite pf WHERE pf.user_id = #{currentUserId} AND pf.post_id = cp.id) as is_favorited " +
            "FROM community_post cp ORDER BY cp.created_at DESC")
    List<CommunityPost> selectAllWithStatsOrderByCreatedAtDesc(Long currentUserId);

    @Select("SELECT cp.*, " +
            "(SELECT COUNT(*) FROM post_like pl WHERE pl.post_id = cp.id AND pl.is_liked = TRUE) as like_count, " +
            "(SELECT COUNT(*) FROM post_favorite pf WHERE pf.post_id = cp.id AND pf.is_favorited = TRUE) as favorite_count, " +
            "(SELECT COUNT(*) FROM post_comment pc WHERE pc.post_id = cp.id) as comment_count, " +
            "(SELECT pl.is_liked FROM post_like pl WHERE pl.user_id = #{currentUserId} AND pl.post_id = cp.id) as is_liked, " +
            "(SELECT pf.is_favorited FROM post_favorite pf WHERE pf.user_id = #{currentUserId} AND pf.post_id = cp.id) as is_favorited " +
            "FROM community_post cp WHERE cp.user_id = #{userId} ORDER BY cp.created_at DESC")
    List<CommunityPost> selectByUserIdWithStatsOrderByCreatedAtDesc(Long userId, Long currentUserId);

    @Select("SELECT cp.*, " +
            "(SELECT COUNT(*) FROM post_like pl WHERE pl.post_id = cp.id AND pl.is_liked = TRUE) as like_count, " +
            "(SELECT COUNT(*) FROM post_favorite pf WHERE pf.post_id = cp.id AND pf.is_favorited = TRUE) as favorite_count, " +
            "(SELECT COUNT(*) FROM post_comment pc WHERE pc.post_id = cp.id) as comment_count, " +
            "(SELECT pl.is_liked FROM post_like pl WHERE pl.user_id = #{currentUserId} AND pl.post_id = cp.id) as is_liked, " +
            "(SELECT pf.is_favorited FROM post_favorite pf WHERE pf.user_id = #{currentUserId} AND pf.post_id = cp.id) as is_favorited " +
            "FROM community_post cp WHERE cp.id = #{id}")
    Optional<CommunityPost> selectByIdWithStats(Long id, Long currentUserId);

    @Update("UPDATE community_post SET title = #{title}, content = #{content}, updated_at = #{updatedAt} WHERE id = #{id}")
    int updateById(CommunityPost post);

    @Update("UPDATE community_post SET like_count = #{likeCount} WHERE id = #{id}")
    int updateLikeCount(Long id, Integer likeCount);

    @Update("UPDATE community_post SET favorite_count = #{favoriteCount} WHERE id = #{id}")
    int updateFavoriteCount(Long id, Integer favoriteCount);

    @Update("UPDATE community_post SET comment_count = #{commentCount} WHERE id = #{id}")
    int updateCommentCount(Long id, Integer commentCount);

    @Delete("DELETE FROM community_post WHERE id = #{id}")
    int deleteById(Long id);

    @Delete("DELETE FROM community_post WHERE user_id = #{userId}")
    int deleteByUserId(Long userId);
}
